set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
with 
 t_collect_in_rate as(
select
collect_scansitecode
,max(collect_inputsite) as collect_inputsite
,order_source --订单来源
,count(1) as center_send_collect_total  --中心发往集散票数
,sum(is_already_flag) as collect_already_send_total --集散已散货总票数
,sum(is_ready_flag) as collect_ready_send_total --集散待散货总票数
,sum(is_wrong_flag) as collect_wrong_send_total --集散错分票数
,sum(is_collect_unintime_unarrival) as collect_unintime_unarrival_total --集散散货不及时且未到件总票数
,sum(is_collect_arrival) as collect_arrival_total --集散到件票数
,cast((sum(is_ready_flag)/(count(1)-sum(is_wrong_flag)))*100 as DECIMAL(13,2)) as is_all_missed_rate  --集散待散占比
,dt as update_time
,is_reback  --是否退转件
,dt
from jms_dm.dm_collect_ready_send_detail
where dt BETWEEN  date_add('{{ execution_date | cst_ds }}',-9) and '{{ execution_date | cst_ds }}'
group by collect_scansitecode,order_source,is_reback,dt
),
t_collect_out_rate as (
 select
 taking_date
,actual_collect_code
,actual_collect_name
,count(1) as should_hand_in_num --应交件明细
,sum(is_delivered) as delivered_cnt --已件件票数
,sum(is_detained) as detained_cnt --滞留总票数
,is_reaback
,order_source
 from jms_dm.dm_collect_ready_intime_detail_dt intime
 where dt BETWEEN  date_add('{{ execution_date | cst_ds }}',-9) and date_add('{{ execution_date | cst_ds }}',-1)
 group by  actual_collect_code ,actual_collect_name ,is_reaback ,order_source,taking_date
 )

insert overwrite table jms_dm.dm_collect_ready_send_rate partition(dt)
select
    net.manage_code  as manage_region_code
    ,net.manage_name as manage_region_name --管理大区名称
    ,net.agent_code as collect_code
    ,net.agent_name as collect_name
    ,net.fran_code as  franchisee_code
    ,net.fran_name as  franchisee_name
    ,t.collect_scansitecode
    ,t.collect_inputsite
    ,t.order_source
    ,t.center_send_collect_total
    ,t.collect_already_send_total
    ,t.collect_ready_send_total
    ,t.collect_wrong_send_total
    ,t.collect_unintime_unarrival_total
    ,t.collect_arrival_total
    ,t.is_all_missed_rate
    ,t.update_time
    ,t.is_reback
    ,t.should_hand_in_num
    ,t.delivered_cnt
    ,t.update_time as dt
from
(
     select
         nvl(inn.update_time,outt.taking_date) as update_time
        ,nvl(inn.collect_scansitecode,outt.actual_collect_code) as collect_scansitecode
        ,nvl(inn.collect_inputsite,outt.actual_collect_name) as collect_inputsite
        ,nvl(inn.order_source,outt.order_source) as order_source --订单来源
        ,inn.center_send_collect_total  --中心发往集散票数  进港-应集散票数
        ,inn.collect_already_send_total --集散已散货总票数
        ,inn.collect_ready_send_total --集散待散货总票数    进港-滞留票数
        ,inn.collect_wrong_send_total --集散错分票数
        ,inn.collect_unintime_unarrival_total --集散散货不及时且未到件总票数
        ,inn.collect_arrival_total --集散到件票数           进港-未到件票数=应集散票数-集散到件票数
        ,inn.is_all_missed_rate  --集散待散占比             进港-滞留率
        ,nvl(inn.is_reback,outt.is_reaback) is_reback  --是否退转件
        ,outt.should_hand_in_num --出港-应交件汇总
        ,outt.delivered_cnt --出港-已件件票数
    from t_collect_in_rate inn
    full join t_collect_out_rate outt
    on inn.collect_scansitecode=outt.actual_collect_code
       and outt.taking_date=inn.update_time
       and inn.is_reback=outt.is_reaback
       and inn.order_source=outt.order_source
)t
left join  jms_dim.dim_network_whole_massage net on t.collect_scansitecode=net.code
distribute by pmod(hash(rand()), 3);
